import pandas as pd

"""
s = pd.Series([1, 2, 3, 4, 5, 6])
print(s)
s = pd.Series([1, True, False, 3.14, 2 + 3j, '帅哥'])
print(s)
dic = {'a': 10, 'b': 20, 'c': 30, 'd': 40}
print(dic)
s = pd.Series(dic)
print(s)
s = pd.Series([1, True, False, 3.14, 2 + 3j, '帅哥'], index=['a', 'b', 'c', 'd', 'e', 'f'])
print(s)
dic = {'name': ['Alice', 'Bob', 'Charlie'], 'age': [25, 30, 35]}
s = pd.Series(dic)
s = pd.DataFrame(dic)
print(s)
data = [['Alice', 25], ['Bod', 30], ['Charlie', 35]]
columns = ['name', 'age']
s = pd.DataFrame(data, columns=columns)
s = pd.Series(data)
print(s)
df = pd.read_excel('grades.xlsx', sheet_name='Sheet1')
print(df)
data = [['张三', '数学', 100], ['李四', '语文', 20], ['王五', 30]]
columns = ['学生姓名', '学科', '成绩']
# df = pd.concat([df, pd.DataFrame(data, columns=columns)])
# df.to_excel('output.xlsx',sheet_name='Sheet1', index=False)
df.info()
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 50 entries, 0 to 49
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   学生姓名    50 non-null     object
 1   学科      50 non-null     object
 2   成绩      50 non-null     int64 
dtypes: int64(1), object(2)
memory usage: 1.3+ KB
'''
rows, cols = df.shape
print(rows, cols)
print(len(df))
print(df.columns)
print(df.index)
print(df.axes)
print(df.dtypes)
print(df.size)
print(df.ndim)
print(df.nunique())
print(df.describe())
print(df.head(n=10))
print(df.tail(n=10))
print(df.to_csv(sep='|', na_rep='none'))
print(df['学生姓名'])
print(df[['学生姓名', '学科', '成绩']])
print(df.loc[0])
print(df.iloc[0])
print(df.loc[0:2, ['学生姓名', '成绩']])
print(df.iloc[0:2, :-1])
# print(df['成绩'] > 90)
s = df['成绩'] > 90
print(s)
# print(df[s])
print(df[s & (df['学科'] == '数学')])
print(df.query("成绩 >= 90 & 学科 == '数学'"))
"""
"""
df = pd.read_excel('students.xlsx', sheet_name='Sheet1')
print(df)
df1 = df.dropna()
print(df1)
df2 = df.fillna({'Age':df['Age'].mean(), 'Gender': '未知', 'Phone':111, 'Address': '西安明德理工学院'})
print(df2)
print(df2['Age'].mean())
df = pd.read_excel('students.xlsx', sheet_name='Sheet1')
print(df)
df1 = df.drop_duplicates(subset='Gender', keep='last')
print(df1)
"""
# df = pd.read_excel('grades.xlsx', sheet_name='Sheet1')
# print(df)
# grouped = df.groupby('学科')
# print(grouped)
# print(type(grouped))
"""
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x00000269B60B1D00>
<class 'pandas.core.groupby.generic.DataFrameGroupBy'>
"""
"""
# for group_name, group_data in grouped:
#     print(group_name)
#     print(group_data)
print(df.groupby('学科')['成绩'].sum())
print(df.groupby('学科')['成绩'].mean())
print(df.groupby('学科')['成绩'].std())
"""
df1 = pd.DataFrame(
    {
        'key': ['A', 'B', 'C', 'D'],
        'value': [1, 2, 3, 4]
    }
)
# print(df1)
df2 = pd.DataFrame(
    {
        'key': ['B', 'D', 'E', 'F'],
        'value': [5, 6, 7, 8]
    }
)
print(df2)
# 内连接 取交集
df = pd.merge(df1, df2, on='key')
print(df)
# 左连接 左边与交集的并集
df = pd.merge(df1, df2, on='key', how='left')
print(df)
# 右连接 右边与交集的并集
df = pd.merge(df1, df2, on='key', how='right')
print(df)
# 外连接 取并集
df = pd.merge(df1, df2, on='key', how='outer')
print(df)

df1 = pd.DataFrame(
    {
        'value1': [1, 2, 3, 4]
    }, index=['A', 'B', 'C', 'D']
)
df2 = pd.DataFrame(
    {
        'value2': [5, 6, 7, 8]
    }, index=['A', 'F', 'C', 'D']
)
# print(df1.join(df2))
# print(df2.join(df1))
# 默认按行
df = pd.concat([df1, df2])
print(df)
df = pd.concat([df1, df2], axis=1)
print(df)

data = {
    'value': [5, 7, 8, 9, 6, 4, 2]
}
index = ['A', 'C', 'E', 'B', 'D', 'G', 'F']
df = pd.DataFrame(data, index=index)
print(df)
#按索引排序
print(df.sort_index())
#按值排序
print(df.sort_values('value'))
#降序
print(df.sort_values('value', ascending=False))
print(df.sort_index(ascending=False))
data = {
    'name':['Alice', 'Bob', 'Charli', 'David'],
    'age':[25, 20, 25, 30],
    'Score':[80, 90, 70, 85]
}
df = pd.DataFrame(data)
print(df)
print(df.sort_values(['age', 'Score']))
print(df.sort_values(['age', 'Score'], ascending=False))
